package cn.ouryears.common.datasource;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

public class BaseDAO {

	private static final Log logger = LogFactory.getLog(BaseDAO.class);
	private JdbcTemplate jdbcTemplate;
	private boolean showSql = true;

	
	
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public boolean isShowSql() {
		return showSql;
	}

	public void setShowSql(boolean showSql) {
		this.showSql = showSql;
	}

	/**
	 * @description 写log
	 * @param message 
	 */	
	public void writeLog(String message) {
		if (showSql || logger.isInfoEnabled())
			logger.info(message);
	}

	/**
	 * @description 调用JDBCTEMPLATE进行插入
	 * @param sqlstr
	 * @return 返回插入的行数
	 */
	public int insert(String sqlstr) {
		writeLog(sqlstr);
		return getJdbcTemplate().update(sqlstr);
	}

	/**
	 * @param sqlstr
	 * @param values
	 * @param types
	 * @return 返回插入的行数
	 */
	public int insert(String sqlstr, Object[] values, int[] types) {
		writeLog(sqlstr);
		return getJdbcTemplate().update(sqlstr, values, types);
	}

	/**
	 * @description 调用jdbcTemplate进行插入
	 * @param sqlstr
	 * @return 返回更新的行数
	 */
	public int update(String sqlstr) {
		writeLog(sqlstr);
		return getJdbcTemplate().update(sqlstr);
	}

	/**
	 * @param sqlstr
	 * @param values
	 * @param types
	 * @return 返回更新的行数
	 */
	public int update(String sqlstr, Object[] parms, int[] types) {
		writeLog(sqlstr);
		return getJdbcTemplate().update(sqlstr, parms, types);
	}

	/**
	 * @description SQL查询
	 * @param sqlstr
	 * @return List
	 */
	@SuppressWarnings("unchecked")
	public List query(String sqlstr) {
		writeLog(sqlstr);
		return getJdbcTemplate().queryForList(sqlstr);
	}

	/**
	 * @param sqlstr
	 * @param values
	 * @param types
	 * @return List
	 */
	@SuppressWarnings("unchecked")
	public List query(String sqlstr, Object[] values, int[] types) {
		writeLog(sqlstr);
		return getJdbcTemplate().queryForList(sqlstr, values, types);
	}

	@SuppressWarnings("unchecked")
	public List query(String sqlstr,RowMapper rowMapper) {
	    writeLog(sqlstr);
        return getJdbcTemplate().query(sqlstr, rowMapper);
	}
	
	/**
	 * @description SQL查询,返回MAP
	 * @param sqlstr
	 * @return Map
	 */
	@SuppressWarnings("unchecked")
	public Map queryForMap(String sqlstr) {
		writeLog(sqlstr);
		return getJdbcTemplate().queryForMap(sqlstr);
	}

	/**
	 * @description SQL查询,返回MAP
	 * @param sqlstr
	 * @return Map
	 */
	@SuppressWarnings("unchecked")
	public Map queryForMap(String sqlstr, Object[] values, int[] types) {
		writeLog(sqlstr);
		List rs = getJdbcTemplate().queryForList(sqlstr, values, types);
		if (rs != null && rs.size() > 0)
			return (Map) rs.get(0);
		return null;
	}

	/**
	 * @description SQL查询,返回总行数
	 * @param sqlstr
	 * @return int
	 */
	public int queryForInt(String sqlstr) {
	    StringBuffer buffer = new StringBuffer();
	    buffer.append("select count(*) as count from (");
	    buffer.append(sqlstr);
	    buffer.append(" )");
	    
		writeLog(buffer.toString());
		return getJdbcTemplate().queryForInt(buffer.toString());
	}

	/**
	 * @description SQL查询,返回总行数
	 * @param sqlstr
	 * @return int
	 */
	public int queryForInt(String sqlstr, Object[] values, int[] types) {
		writeLog(sqlstr);
		return getJdbcTemplate().queryForInt(sqlstr, values, types);
	}

	/**
	 * @description SQL查询,返回总行数,类型为Long
	 * @param sqlstr
	 * @return long
	 */
	public long queryForLong(String sqlstr) {
		writeLog(sqlstr);
		return getJdbcTemplate().queryForLong(sqlstr);
	}
	
	/**
	 * @description SQL oracle 分页查询
	 * @param sqlStr
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List queryForPage(String sqlstr, long starrow, long endrow) {

		sqlstr = getPageSql(sqlstr, starrow, endrow);
		writeLog(sqlstr);
		return getJdbcTemplate().queryForList(sqlstr);
	}

	public String getPageSql(String sqlstr, long starrow, long rows) {
	    StringBuffer strBuffer = new StringBuffer();
	    
	    strBuffer.append("SELECT * FROM ( SELECT row_.*, rownum rownum_ FROM (");
	    strBuffer.append(sqlstr);
	    strBuffer.append(") row_ WHERE rownum <= ");
	    strBuffer.append(starrow+rows);
	    strBuffer.append(") WHERE rownum_ >");
	    strBuffer.append(starrow);
	    
		return strBuffer.toString();
	}
	
	/**
	 * 获取序列值
	 * @author ajm
	 * @param seqName 序列名
	 * @return
	 */
	public long getLongPrimaryKey(String seqName) {
		String sqlstr = "select " + seqName + ".Nextval from dual";
		writeLog(sqlstr);
		return getJdbcTemplate().queryForLong(sqlstr);
	}

	/**
	 * @param seqName 序列名
	 * @return
	 */
	public int getIntPrimaryKey(String seqName) {
		String sqlstr = "select " + seqName + ".Nextval from dual";
		writeLog(sqlstr);
		return getJdbcTemplate().queryForInt(sqlstr);
	}

	/**
	 * 
	 * @param sql
	 * @param setter
	 * @return 批量操作影响行数
	 */
	public int batchUpdate(String sql, BatchPreparedStatementSetter setter) {
		writeLog(sql);
		int[] batchResult = getJdbcTemplate().batchUpdate(sql, setter);
		int rowsAffected = 0;
		for (int i = 0; i < batchResult.length; i++) {
			if (batchResult[i] > 0
					|| batchResult[i] == PreparedStatement.SUCCESS_NO_INFO)
				rowsAffected += 1;
		}

		return rowsAffected;
	}

	public String getBlob(final String sql, final String blobField,
			final String idVal) {
		if (StringUtils.isEmpty(sql) || StringUtils.isEmpty(blobField)
				|| StringUtils.isEmpty(idVal) || StringUtils.isEmpty(idVal))
			return null;

		ConnectionCallback queryCallback = new ConnectionCallback() {
			public Object doInConnection(Connection con) throws SQLException,
					DataAccessException {
				PreparedStatement pstmt = con.prepareStatement(sql);
				pstmt.setString(1, idVal);
				ResultSet rset = pstmt.executeQuery();
				StringBuffer content = new StringBuffer();
				if (rset.next()) {
					Clob contentClob = (Clob) rset.getClob(blobField);
					if (contentClob != null) {
						Reader r = contentClob.getCharacterStream();
						BufferedReader br = new BufferedReader(r);
						try {
							String line = br.readLine();
							while (line != null) {
								content.append(line);
								line = br.readLine();
							}
						} catch (IOException e) {
							e.printStackTrace();
						}
					}
				}
				return content;
			}

		};
		return ((StringBuffer) getJdbcTemplate().execute(queryCallback))
				.toString();
	}
	
	public void execute(String sql){
		getJdbcTemplate().execute(sql);
	}
}
